/*
 * To change this license header, choose License Headers in Project Properties.
 * To change this template file, choose Tools | Templates
 * and open the template in the editor.
 */

package Server.ImplementInterface;

import DAL.DBManager;
import Server.DTO.MonthlyReport_DTO;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.ArrayList;
import java.util.Date;

/**
 *
 * @author 10520_000
 */
public class MonthlyReportRepository {
    public static int Insert(MonthlyReport_DTO info){
        int iRet = -1;
        try {
            Connection con = DBManager.getInstance().getConnection();
            String SQL = "INSERT INTO MONTHLYREPORT(ReportID , ReportingDate,TypeID) Values(?,?)";
            PreparedStatement pstmt = con.prepareStatement(SQL);
            pstmt.setString(1, info.getReportID());
            pstmt.setDate(2, (java.sql.Date) (Date) info.getReportingMonth());
            pstmt.setString(3, info.getSavingTypeID());
            iRet = pstmt.executeUpdate();
            pstmt.close();
        } catch (SQLException se) {
            System.out.println(se);
        }
        return iRet;
    }
    
    public static int Update(MonthlyReport_DTO info){
        int iRet = -1;
        try {
            Connection con = DBManager.getInstance().getConnection();
            String SQL = "UPDATE MONTHLYREPORT SET ReportID= ?,ReportingDate= ?,TypeID= ? WHERE ReportID= ?";
            PreparedStatement pstmt = con.prepareStatement(SQL);
            pstmt.setString(1, info.getReportID());
            pstmt.setDate(2, (java.sql.Date) (Date) info.getReportingMonth());
            pstmt.setString(3, info.getSavingTypeID());
            iRet = pstmt.executeUpdate();
            pstmt.close();
        } catch (SQLException se) {
            System.out.println(se);
        }
        return iRet;
    }
    
    public static int Delete(MonthlyReport_DTO info){
        int iRet = -1;
        try {
            Connection con = DBManager.getInstance().getConnection();
            String SQL = "DELETE MONTHLYREPORT WHERE ReportID= ?";
            PreparedStatement pstmt = con.prepareStatement(SQL);
            pstmt.setString(1, info.getReportID());
            pstmt.setDate(2, (java.sql.Date) (Date) info.getReportingMonth());
            iRet = pstmt.executeUpdate();
            pstmt.close();
        } catch (SQLException se) {
            System.out.println(se);
        }
        return iRet;
    }
    
    public static ArrayList FindAll() {
        ArrayList arr = new ArrayList();

        try {
            String sql = "SELECT * FROM MONTHLYREPORT ORDER BY ReportID";
            Connection con = DBManager.getInstance().getConnection();
            Statement stmt = con.createStatement();
            ResultSet rs = stmt.executeQuery(sql);

            while (rs.next()) {
                MonthlyReport_DTO info = new MonthlyReport_DTO();
                info.setReportID(rs.getString("ReportID"));
                info.setReportingMonth(rs.getDate("ReportingDate"));         
                info.setSavingTypeID(rs.getString("TypeID"));
                arr.add(info);
            }

            stmt.close();
        } catch (SQLException se) {
            System.out.println(se);
        }
        return arr;
    }
    
    public static ArrayList FindbyID(String ID) {
        ArrayList arr = new ArrayList();

        try {
            String QRY = "SELECT * FROM MONTHLYREPORT WHERE ReportID LIKE(?) ORDER BY ReportID";
            Connection con = DBManager.getInstance().getConnection();
            PreparedStatement pstmt = con.prepareStatement(QRY);
            pstmt.setString(1, "%" + ID + "%");
            ResultSet rs = pstmt.executeQuery();

            while (rs.next()) {
                MonthlyReport_DTO info = new MonthlyReport_DTO();
                info.setReportID(rs.getString("ReportID"));
                info.setReportingMonth(rs.getDate("ReportingDate"));         
                info.setSavingTypeID(rs.getString("TypeID"));                
                arr.add(info);
            }
            pstmt.close();
        } catch (SQLException se) {
            System.out.println(se);
        }
        return arr;
    }
}
